/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package org.dusin.teachassistdbservice.dao4mysql;

import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.dusin.teachassistdbservice.dao.StudentDao;
import org.springframework.dao.EmptyResultDataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author dusin
 */
public class StudentDaoImpl implements StudentDao{
    static Logger logger = Logger.getLogger(StudentDaoImpl.class);
    private JdbcTemplate jdbcTemplate;

    public StudentDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    
    @Override
    public boolean doCreate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "insert into Student(studentno,name,pinyin,password) values(?,?,?,?)";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    //根据学号删除
    @Override
    public boolean doDeleteByNo(String no) {
        boolean flag = true;
        String sql = null;
        sql = "delete from student where studentno=?";
        int rows = jdbcTemplate.update(sql, no);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    
    @Override
    public boolean doUpdate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "update student set name=?,pinyin=? where studentno=?";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
     
    @Override
    public boolean doUpdatePassword(String no,String password) {
        boolean flag = true;
        String sql = null;
        sql = "update student set password=? where studentno=?";
        int rows = jdbcTemplate.update(sql, password,no);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public Map<String, Object> findByNo(String no) {
        String sql = null;
        try{
            sql = "select * from  student where studentno = ?";
            return jdbcTemplate.queryForMap(sql,no);        
        }catch(EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public Map<String,Object> login(String username,String password){        
        String sql = null;
        sql = "select * from  student where studentno = ?  and password=?";
        return jdbcTemplate.queryForMap(sql,username,password);        
    }
    @Override
    public List<Map<String, Object>> findAll() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAll(int start, int size) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public boolean doCourse(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "insert into studentclass(no,classid,studentno,note) values(?,?,?,?)";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public boolean doDeleteCourse(String no,int teachclassid){
        boolean flag = true;
        String sql = null;
        sql = "delete from studentclass where studentno=? and classID=? ";
        int rows = jdbcTemplate.update(sql, no,teachclassid);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doUpdateCourse(String sno,int teachclassid,int no,String note){
        boolean flag = true;
        String sql = null;
        sql = "update studentclass set note=? where studentno=? and classID=? ";
        int rows = jdbcTemplate.update(sql, note,no,teachclassid);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doUpdateNote(String no,int teachclassid,String note){
        boolean flag = true;
        String sql = null;
        sql = "update studentclass set note=? where studentno=? and classID=? ";
        int rows = jdbcTemplate.update(sql, note,no,teachclassid);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public List<Map<String, Object>> findAllByClassID(int classID) {
        boolean flag = true;
        String sql = null;
        sql = "select no,studentclass.studentno,name,pinyin,note from studentclass left join student on studentclass.studentno=student.studentno where classID = ? order by studentno";
        return jdbcTemplate.queryForList(sql,classID);
    }    
    
    @Override
    public List<Map<String,Object>> findAttendanceDetail(int teachclassId,int attendanceId){        
        String sql = null;
        sql = "select no,t1.studentno,name,pinyin ,ifnull(status,0) status,createtime,anote note from (select no,studentclass.studentno,name,pinyin from studentclass left join student on studentclass.studentno=student.studentno where classID = ? order by no) t1 left join (select studentno,status,createtime,note as anote from attendancedetail where attendanceid=?) t2 on t1.studentno = t2.studentno;";
        return jdbcTemplate.queryForList(sql,teachclassId,attendanceId);
    }
}
